Relational databases, in which various tables of data are inter-related through fields that occur in the different related tables, are well known in the art. The use of structured query language (SQL) by which to instruct a database manager to extract data from a relational database is also known in the art. See for example U.S. Pat. No. 5,519,859 to Grace for a Method and Apparatus for Automatic Table Selection and Generation of Structured Query Language Instructions.
The structure of the tables and fields in a database is often diagrammed in a tree structure. A tree is a special form of directed graph, which generally commences at a distinguished vertex called the root. The root has no predecessors. Every vertex other than the root has a unique predecessor. Vertices (or nodes) of a tree that have successors are called non-terminal vertices, or parent nodes. Vertices that have no successors are called terminal vertices, or leaves. All nodes that have a parent (i.e., all nodes except the root node) are referred to as child nodes. The tree terminology set forth above (i.e., root, node, leaf, etc.) is often used in describing the structure of a relational database.
The ease of use and general applicability of relational databases have resulted in their being used extensively, in many different environments. Business, especially, has found relational databases appropriate for its needs, including in situations where even a very large amount of data is stored and maintained. With a large amount of data, because of how queries of a relational database are performed according to the prior art, the processing needed to respond to some queries can take a large amount of time. A typical query refers to several tables of a relational database. The time required to process a query is typically not linearly related to the number of fields in the relational database, but instead is roughly related to the product of the number of fields in each table referred to in a query.
Many relational databases have what is called a star configuration in which one table, called the hub table, is related to each of the other tables of the relational database; the other tables are each referred to as dimension tables. For example, referring to FIG. 1, a relational database is shown including tables A (Sales), B (Customers), C (Salespersons), and D (Products). The B, C and D tables are all related to the A (Sales) table by each having a field that corresponds to a field in the sales table, which is therefore the hub table, and the B, C, and D tables are therefore each a dimension table. For example, table C (the Salespersons table) is related to the hub table (the sales table) through the field having the fieldname SalesPersonID.
Another relational database management structure (RDBMS) is known as a snowflake configuration. As will be appreciated by those skilled in the art, snowflake is an alternative wide configuration for a RDBMS structure of tables.
To create a report showing the total sales for each product, for each customer, and for each salesperson requires, according to the prior art, that the database manager responding to the query perform a search of the database in which each row of the Sales table is examined, and for each row of the Sales table, each of the dimension tables is also examined. To provide a report in which the salesperson is not included would require looking at fewer rows of tables of the database; the reduction would be roughly by a factor equal to the number of rows of the Salespersons table, for each row of the Sales table. In providing the report, for each row of the Sales table, the database manager refers to the Customers table to find the customer name based on the customer ID, and then to the Products table to find the product name given the product ID, and then to the Salespersons table to find the sales person name given the salesperson ID. The report layout is indicated in FIG. 2. In preparing a report for such a database, the database manager must examine a total number of rows of tables roughly (order of magnitude) equal to the product of the number of rows in each table. (For each row of the hub table, for each field occurring in a dimension table, the database manager must perform a search of the dimension table to find the field value referred to, a search that typically involves looking at a substantial number of the rows of the dimension table.)
Although today's processing power is substantial, and increasing still, the volume of data in many relational databases is such that sometimes burdensome amounts of time must be allocated to preparing reports, the greater the number of dimension tables referred to in a query, the longer the processing time needed by the database manager responding to the query.
What is needed is a way to provide a report, in response to a query that includes fields from the hub table and at least some dimension tables of e.g., a star or snowflake relational database, that does not require that for each row of the hub table the database manager examine the dimension tables referred to, yet provides the same report as would be provided according to the prior art, and therefore doing so in substantially less processing time.